CREATE TABLE Category
(
  CategoryId    nvarchar(150)   NOT NULL    ,
  Name    nvarchar(150)   NOT NULL    ,
  Descn    nvarchar(150)   NULL    ,
    CONSTRAINT PK_Category PRIMARY KEY (CategoryId)
)
;

CREATE TABLE Inventory
(
  ItemId    nvarchar(150)   NOT NULL    ,
  Qty    int   NOT NULL    ,
    CONSTRAINT PK_Inventory PRIMARY KEY (ItemId)
)
;

CREATE TABLE Item
(
  ItemId    nvarchar(150)   NOT NULL    ,
  ProductId    nvarchar(150)   NOT NULL    ,
  ListPrice    decimal   NULL    ,
  UnitCost    decimal   NULL    ,
  Supplier    int   NULL    ,
  Status    nvarchar(150)   NULL    ,
  Name    nvarchar(150)   NULL    ,
  Image    nvarchar(150)   NULL    ,
    CONSTRAINT PK_Item PRIMARY KEY (ItemId)
)
;

CREATE TABLE Product
(
  ProductId    nvarchar(150)   NOT NULL    ,
  CategoryId    nvarchar(150)   NOT NULL    ,
  Name    nvarchar(150)   NULL    ,
  Descn    nvarchar(150)   NULL    ,
  Image    nvarchar(150)   NULL    ,
    CONSTRAINT PK_Product PRIMARY KEY (ProductId)
)
;

CREATE TABLE Supplier
(
  SuppId    int   NOT NULL    AUTO_INCREMENT,
  Name    nvarchar(150)   NULL    ,
  Status    nvarchar(150)   NOT NULL    ,
  Addr1    nvarchar(150)   NULL    ,
  Addr2    nvarchar(150)   NULL    ,
  City    nvarchar(150)   NULL    ,
  State    nvarchar(150)   NULL    ,
  Zip    nvarchar(150)   NULL    ,
  Phone    nvarchar(150)   NULL    ,
    CONSTRAINT PK_Supplier PRIMARY KEY (SuppId)
)
;

CREATE TABLE DAC_ISSUE
(
  ISSUE_ID    int   NOT NULL    AUTO_INCREMENT,
  ISSUE_TITLE    nvarchar(255)   NOT NULL    ,
  ISSUE_DESC    nvarchar(2000)   NULL    ,
  ISSUE_STS    int   NOT NULL    ,
  ISSUE_TYP    nvarchar(50)   NULL    ,
  ISSUE_OWNR    nvarchar(50)   NULL    ,
  ISSUE_PRGS    decimal(18, 2)   NULL    ,
  CREATE_TM    datetime   NOT NULL    ,
  ISSUE_LOCKED    int   NULL    ,
    CONSTRAINT PK_DAC_ISSUE PRIMARY KEY (ISSUE_ID)
)
;

CREATE TABLE DAC_USER
(
  USER_ID    nvarchar(50)   NOT NULL    ,
  USERNAME    nvarchar(50)   NOT NULL    ,
  FULLNAME    nvarchar(255)   NULL    ,
  USER_PWD    nvarchar(50)   NULL    ,
  CHANGINGPWD    int   NULL    ,
  USERLOCKED    int   NULL    ,
  LASTLOGIN    datetime   NULL    ,
  USER_DESC    nvarchar(2000)   NULL    ,
    CONSTRAINT PK_DAC_USER PRIMARY KEY (USER_ID)
)
;

CREATE TABLE DAC_MUTIPLEKEYS
(
  DAC_KEY1    nvarchar(50)   NOT NULL    ,
  DAC_KEY2    nvarchar(50)   NOT NULL    ,
  DAC_VALUE1    nvarchar(50)   NULL    ,
  DAC_VALUE2    nvarchar(50)   NULL    ,
    CONSTRAINT PK_DAC_MUTIPLEKEYS PRIMARY KEY (DAC_KEY1, DAC_KEY2)
)
;

CREATE PROCEDURE `add`(a int, b int)
BEGIN
    declare c int;   
    if a is null then   
    set a = 0;   
    end if;   
    if b is null then   
    set b = 0;   
    end if;   
    set c = a + b;   
    select c as sum; 
END
;

CREATE PROCEDURE `addc`(a int, b int, out c int)
BEGIN    
    if a is null then   
    set a = 0;   
    end if;   
    if b is null then   
    set b = 0;   
    end if;   
    set c = a + b;
END
;
